-- @owner: @zou_jialiang050
-- @date: 2023/2/20
-- @testpoint: create function 使用原有语句创建包含case控制语句函数
-- @modified by @zou_jialiang050 2024/4/30:根据最新代码优化用例

--step1:建表插数据;expect:成功
drop table if exists t_create_function_0038;
create table t_create_function_0038(cid int, account decimal(7,3));
insert into t_create_function_0038 values(001, 666.66);

--step2:create function 使用原有语句创建包含case控制语句函数;expect:成功
drop function if exists f_create_function_0038;
create function f_create_function_0038(card_id int) returns char(50) as
$$
declare a char(50);
declare money decimal(7,3);
declare num int;
begin
select account into money from t_create_function_0038 where cid = card_id;
set num = truncate(money/100,0);
case num
when 0 then set a=1;
when 1 then set a=2;
else set a=3;
end case;
return a;
end;
$$ language plpgsql;
/
select f_create_function_0038(1);

--step3:清理环境;expect:成功
drop function if exists f_create_function_0038;
drop table if exists t_create_function_0038;